package de.hijacksoft.isle.adapters;

import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

import static de.hijacksoft.isle.classes.Variables.*;
import de.hijacksoft.isle.classes.Category;
import de.hijacksoft.isle.classes.Item;
import de.hijacksoft.isle.R;
import de.hijacksoft.isle.classes.Recipe;
import java.util.Collections;

public class DBAdapter {

    private static final String TAG = "DBAdapter";
    public static final String DATABASE_NAME = "isledb";
    // TABLE NAMES
    public static final String TABLE_MAIN = "listtable";
    public static final String TABLE_ITEM = "itemtable";
    public static final String TABLE_RECIPE = "recipetable";
    public static final String TABLE_HISTORY = "historytable";
    public static final String TABLE_CATEGORY = "categorytable";
    // TABLE:MAIN COLUMNS
    public static final int MAIN_COLUMN_ITEMID = 0;
    public static final int MAIN_COLUMN_AMOUNT = 1;
    public static final int MAIN_COLUMN_BOUGHT = 2;
    public static final int MAIN_COLUMN_CATEGORY = 3;
    // TABLE:ITEM COLUMNS
    public static final int ITEM_COLUMN_ID = 0;
    public static final int ITEM_COLUMN_NAME = 1;
    public static final int ITEM_COLUMN_AMOUNT = 2;
    public static final int ITEM_COLUMN_BOUGHT = 3;
    public static final int ITEM_COLUMN_PRICE = 4;
    public static final int ITEM_COLUMN_LASTBOUGHT = 5;
    public static final int ITEM_COLUMN_PERIOD = 6;
    public static final int ITEM_COLUMN_PHOTO = 7;
    public static final int ITEM_COLUMN_BARCODE = 8;
    public static final int ITEM_COLUMN_CATEGORY = 9;
    // TABLE:RECIPE COLUMNS
    public static final int RECIPE_COLUMN_ID = 0;
    public static final int RECIPE_COLUMN_NAME = 1;
    public static final int RECIPE_COLUMN_ITEMS = 2;
    public static final int RECIPE_COLUMN_AMOUNT = 3;
    public static final int RECIPE_COLUMN_LASTBOUGHT = 4;
    public static final int RECIPE_COLUMN_RATING = 5;
    // TABLE:HISTORY COLUMNS
    public static final int HISTORY_COLUMN_ID = 0;
    public static final int HISTORY_COLUMN_ITEMID = 1;
    public static final int HISTORY_COLUMN_AMOUNT = 2;
    public static final int HISTORY_COLUMN_BOUGHT = 3;
    // TABLE:CATEGORY COLUMNS
    public static final int CATEGORY_COLUMN_ID = 0;
    public static final int CATEGORY_COLUMN_NAME = 1;
    public static final int CATEGORY_COLUMN_COLOR = 2;
    public static final int CATEGORY_COLUMN_ISRECIPE = 3;
    // KEYS
    public static final String KEY_ID = "_id";
    public static final String KEY_ITEMID = "itemid";
    public static final String KEY_CATEGORYID = "category";
    public static final String KEY_NAME = "name";
    public static final String KEY_AMOUNT = "amount";
    public static final String KEY_PRICE = "price";
    public static final String KEY_LASTBOUGHT = "lastdate";
    public static final String KEY_RATING = "rating";
    public static final String KEY_PERIOD = "period";
    public static final String KEY_PHOTO = "photo";
    public static final String KEY_ITEMS = "items";
    public static final String KEY_BOUGHT = "bought";
    public static final String KEY_BARCODE = "barcode";
    public static final String KEY_COLOR = "color";
    public static final String KEY_RECIPE = "recipe";
    // DB VERSION
    public static final int DATABASE_VERSION = 1;
    private SQLiteDatabase db;
    private SQLiteOpenHelper helper;
    private Context context;
    private boolean isOpen = false;
    private ArrayList<Cursor> unclosedCursors = new ArrayList<Cursor>();

    public DBAdapter(Context context) {
        this.context = context;

        helper = new SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

            @Override
            public void onCreate(SQLiteDatabase db) {
                if (db == null) {
                    return;
                }
                db.execSQL("CREATE TABLE " + TABLE_MAIN + "(" + KEY_ITEMID + " INTEGER, " + KEY_AMOUNT + " TEXT, " + KEY_BOUGHT + " INTEGER, " + KEY_CATEGORYID + " INTEGER);");
                db.execSQL("CREATE TABLE " + TABLE_ITEM + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME + " TEXT NOT NULL, " + KEY_AMOUNT + " TEXT, " + KEY_BOUGHT + " INTEGER, " + KEY_PRICE + " FLOAT, " + KEY_LASTBOUGHT + " LONG, " + KEY_PERIOD + " FLOAT, " + KEY_PHOTO + " TEXT, " + KEY_BARCODE + " LONG, " + KEY_CATEGORYID + " INTEGER );");
                db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_ITEMID + " INTEGER, " + KEY_AMOUNT + " TEXT, " + KEY_BOUGHT + " LONG);");
                db.execSQL("CREATE TABLE " + TABLE_RECIPE + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME + " TEXT NOT NULL, " + KEY_ITEMS + " TEXT, " + KEY_AMOUNT + " TEXT, " + KEY_LASTBOUGHT + " LONG, " + KEY_RATING + " FLOAT);");
                db.execSQL("CREATE TABLE " + TABLE_CATEGORY + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME + " TEXT, " + KEY_COLOR + " INTEGER, " + KEY_RECIPE + " INTEGER);");
            }

            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                // NOT NEEDED YET
            }
        };
    }
    
    public int getRecipeCount(){
        if(isOpen()){
            Cursor cursor = db.query(TABLE_RECIPE, null, null, null, null, null, null);
            unclosedCursors.add(cursor);

            int count = cursor.getCount();

            cursor.close();
            unclosedCursors.remove(cursor);
            return count;
        }
        else {
            open();
            return getRecipeCount();
        }
    }

    public ArrayList<Integer> getRecipeIDs(){

        Cursor cursor = db.query(TABLE_RECIPE, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        
        ArrayList<Integer> ids = ids = new ArrayList();
        
        if (cursor.moveToFirst()) {
            do {
                ids.add(cursor.getInt(RECIPE_COLUMN_ID));
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        unclosedCursors.remove(cursor);
        
        return ids;
    }
    
    public ArrayList<Integer> getGenerateableItemIDs(){
        long time = System.currentTimeMillis();

        String query = "SELECT * FROM " + TABLE_ITEM + " WHERE " + KEY_LASTBOUGHT + " <= \"" + (time - 2*ONE_DAY) + "\"";
        Cursor cursor = db.rawQuery(query, null);
        unclosedCursors.add(cursor);
        
        ArrayList<Integer> ids = ids = new ArrayList();
        
        if (cursor.moveToFirst()) {
            do {
                if(!((time - cursor.getLong(ITEM_COLUMN_LASTBOUGHT))/ONE_DAY > cursor.getInt(ITEM_COLUMN_PERIOD))){
                    ids.add(cursor.getInt(ITEM_COLUMN_ID));
                }
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        unclosedCursors.remove(cursor);
        
        return ids;
    }
    
    public int getGenerateableItemsCount(){
        if(isOpen()){
            long time = System.currentTimeMillis();

            String query = "SELECT * FROM " + TABLE_ITEM + " WHERE " + KEY_LASTBOUGHT + " <= \"" + (time - 2*ONE_DAY) + "\" AND " + KEY_PERIOD + " < \"" + 365 + "\"";
            Cursor cursor = db.rawQuery(query, null);
            unclosedCursors.add(cursor);

            int count = cursor.getCount();

            if (cursor.moveToFirst()) {
                do {
                    if(((time - cursor.getLong(ITEM_COLUMN_LASTBOUGHT))/ONE_DAY > cursor.getInt(ITEM_COLUMN_PERIOD))) count--;
                } while (cursor.moveToNext());
            }

            cursor.close();
            unclosedCursors.remove(cursor);
            return count;
        }
        else{
            open();
            return getGenerateableItemsCount();
        }
    }
    
    public ArrayList<Category> getCategories() {
        Cursor cursor = db.query(TABLE_CATEGORY, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        ArrayList<Category> list = new ArrayList<Category>();

        try {

            Log.d(TAG, "tying to fetch categories");

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    Category cat = new Category();
                    cat.id = cursor.getInt(CATEGORY_COLUMN_ID);
                    cat.name = cursor.getString(CATEGORY_COLUMN_NAME);
                    cat.color = cursor.getInt(CATEGORY_COLUMN_COLOR);
                    cat.isRecipe = cursor.getInt(CATEGORY_COLUMN_ISRECIPE) == 1;
                    list.add(cat);
                    Log.d(TAG, "fetched Category:" + cat.toString() + ", " + cat.id + ", " + cat.isRecipe);
                } while (cursor.moveToNext());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        return list;
    }

    /**
     * check if the adapter is currently opened
     * @return returns true if open
     */
    public boolean isOpen() {
        return isOpen;
    }

    /**
     * opens the database adapter
     */
    public void open() {
        try {
            db = helper.getWritableDatabase();
            isOpen = true;
        } catch (Exception e) {
            Log.v(TAG, e.toString());
        }
    }

    /**
     * closes the database adapter
     */
    public void close() {
        if (db == null) {
            return;
        }
        for (Cursor cursor : unclosedCursors) {
            if (cursor != null) {
                cursor.close();	// close all cursors on db close
            }
        }
        db.close();
        isOpen = false;
    }

    /**
     * Try to get a category by id from the TABLE_CATEGORY
     * @param id the id of the item
     * @return returns an item if an item with the given id is in the table, returns null otherwise
     */
    public Category getCategoryById(int id) {
        String query = "SELECT * FROM " + TABLE_CATEGORY + " WHERE " + KEY_ID + " = \"" + id + "\"";
        Cursor cursor = db.rawQuery(query, null);
        unclosedCursors.add(cursor);
        Category cat = null;
        if (cursor.moveToFirst()) {
            cat = new Category();
            cat.id = cursor.getInt(CATEGORY_COLUMN_ID);
            cat.name = cursor.getString(CATEGORY_COLUMN_NAME);
            cat.color = cursor.getInt(CATEGORY_COLUMN_COLOR);
            cat.isRecipe = cursor.getInt(CATEGORY_COLUMN_ISRECIPE) == 1;
        } else {
            // set category to uncategorized if no category was found
            cat = new Category(Category.UNCATEGORIZED, context.getString(R.string.uncategorized), 0xaaaaaa);
        }
        // close cursors and return
        cursor.close();
        unclosedCursors.remove(cursor);
        return cat;
    }

    /**
     * Try to get an item by id from the TABLE_ITEM
     * @param id the id of the item
     * @return returns an item if an item with the given id is in the table, returns null otherwise
     */
    public Item getItemById(int id) {
        String query = "SELECT * FROM " + TABLE_ITEM + " WHERE " + KEY_ID + " = \"" + id + "\"";
        Cursor cursor = db.rawQuery(query, null);
        unclosedCursors.add(cursor);
        Item item = new Item();
        item.name = context.getString(R.string.notfound);
        if (cursor.moveToFirst()) {
            Log.d(TAG, "new item.amount: " + cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT));
            item = new Item(
                    cursor.getString(DBAdapter.ITEM_COLUMN_NAME),
                    cursor.getFloat(DBAdapter.ITEM_COLUMN_AMOUNT),
                    Double.parseDouble(cursor.getString(DBAdapter.ITEM_COLUMN_PRICE)),
                    cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).charAt(cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).length() - 1),
                    Uri.parse(cursor.getString(DBAdapter.ITEM_COLUMN_PHOTO)),
                    cursor.getLong(DBAdapter.ITEM_COLUMN_LASTBOUGHT),
                    cursor.getInt(DBAdapter.ITEM_COLUMN_PERIOD));
            item.id = id;
            item.barcode = cursor.getLong(DBAdapter.ITEM_COLUMN_BARCODE);
            item.boughtcount = cursor.getInt(DBAdapter.ITEM_COLUMN_BOUGHT);
            item.category = getCategoryById(cursor.getInt(ITEM_COLUMN_CATEGORY));
            cursor.close();
            unclosedCursors.remove(cursor);
            return item;
        }else{
            cursor.close();
            unclosedCursors.remove(cursor);
            return null;
        }
    }

    /**
     * Try to get a Recipe by id from the TABLE_RECIPE
     * @param id the recipe id
     * @return returns a recipe if a recipe with the given id is in the table, returns null otherwise
     */
    public Recipe getRecipeById(int id) {
        String query = "SELECT * FROM " + TABLE_RECIPE + " WHERE " + KEY_ID + " = \"" + id + "\"";
        Cursor cursor = db.rawQuery(query, null);
        unclosedCursors.add(cursor);

        if (cursor.moveToFirst()) {
            Recipe rec = new Recipe(cursor.getString(RECIPE_COLUMN_NAME));
            try {
                rec.rating = Double.parseDouble(cursor.getString(RECIPE_COLUMN_RATING));
            } catch (Exception e) {
                Log.i(TAG, "couldnt parse the rating from db, this is propably because the recipe is not rated yet");
            }
            rec.lastBought = cursor.getLong(RECIPE_COLUMN_LASTBOUGHT);
            rec.id = cursor.getInt(RECIPE_COLUMN_ID);
            String ingreds = cursor.getString(RECIPE_COLUMN_ITEMS);
            String amounts = cursor.getString(RECIPE_COLUMN_AMOUNT);
            Pattern p = Pattern.compile("\\d*\\.\\w*");
            Matcher m = p.matcher(amounts);
            ArrayList<String> amountList = new ArrayList<String>();
            while (m.find()) {
                amountList.add(m.group());
            }
            p = Pattern.compile("\\d+");
            m = p.matcher(ingreds);
            int i = 0;
            while (m.find()) {
                Item item = getItemById(Integer.parseInt(m.group()));
                double oldAmount = item.amount;

                Pattern q = Pattern.compile("\\d*\\.\\d*");
                Matcher n = q.matcher(amountList.get(i));

                if (n.find()) {
                    Log.d(TAG, n.group());
                    item.amount = Double.parseDouble(n.group());
                }
                Log.d(TAG, "amount#" + i + ": " + amountList.get(i));
                item.price *= item.amount / oldAmount;
                item.unit = amountList.get(i).charAt(amountList.get(i).length() - 1);
                item.category = getCategoryById(getCategoryIdByName(rec.name));
                i++;
                rec.addItem(item);
            }
            cursor.close();
            unclosedCursors.remove(cursor);
            Log.v(TAG, "fetched recipe from db: " + rec.name + " (" + rec.id + "), " + rec.rating);
            return rec;
        } else {
            return null;
        }
    }

    /**
     * get a recipe by name 
     * @return returns a recipe with the given name. returns uncategorized otherwise
     */
    public Category getCategoryByName(String name) {
        return getCategoryById(getCategoryIdByName(name));
    }

    /**
     * get the complete list of items in the history tablse
     * @return returns a list with all items from the history table, returns an empty list if no items are in the history table.
     */
    public ArrayList<Item> getHistory() {

        Cursor cursor = db.query(TABLE_HISTORY, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        ArrayList<Item> list = new ArrayList<Item>();

        try {

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    Item item = this.getItemById(cursor.getInt(HISTORY_COLUMN_ITEMID));
                    item.amount = Double.parseDouble(cursor.getString(HISTORY_COLUMN_AMOUNT).replaceAll("[a-z]*", ""));
                    item.lastbought = cursor.getLong(HISTORY_COLUMN_BOUGHT);
                    item.historyid = cursor.getInt(HISTORY_COLUMN_ID);
                    list.add(0, item);
                } while (cursor.moveToNext());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        return list;

    }

    /**
     * add an item to the history table
     * @param item the item to add to the history
     */
    public void addItemToHistory(Item item) {
        db.execSQL("INSERT INTO " + TABLE_HISTORY + " (" + KEY_ITEMID + ", " + KEY_AMOUNT + ", " + KEY_BOUGHT + ") VALUES (\"" + item.id + "\", \"" + item.amount + "" + item.unit + "\", \"" + item.lastbought + "\");");
    }

    /**
     * get all items from the TABLE_ITEMS table
     * @return returns a list with all items, returns an empty list when no items are in the table.
     */
    public ArrayList<Item> getAllItems() {

        Cursor cursor = db.query(TABLE_ITEM, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        ArrayList<Item> list = new ArrayList<Item>();

        try {

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    Item item = new Item(
                            cursor.getString(DBAdapter.ITEM_COLUMN_NAME),
                            cursor.getFloat(DBAdapter.ITEM_COLUMN_AMOUNT),
                            Double.parseDouble(cursor.getString(DBAdapter.ITEM_COLUMN_PRICE).replaceFirst("[a-z]", "")),
                            cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).charAt(cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).length() - 1),
                            Uri.parse(cursor.getString(DBAdapter.ITEM_COLUMN_PHOTO)),
                            cursor.getLong(DBAdapter.ITEM_COLUMN_LASTBOUGHT),
                            cursor.getFloat(DBAdapter.ITEM_COLUMN_PERIOD));
                    item.id = cursor.getInt(DBAdapter.ITEM_COLUMN_ID);
                    item.barcode = cursor.getLong(DBAdapter.ITEM_COLUMN_BARCODE);
                    item.boughtcount = cursor.getInt(DBAdapter.ITEM_COLUMN_BOUGHT);
                    item.category = getCategoryById(cursor.getInt(ITEM_COLUMN_CATEGORY));
                    list.add(0, item);
                } while (cursor.moveToNext());
            }

            // sort the list
            Collections.sort(list);

        } catch (Exception e) {
            e.printStackTrace();
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        Log.d(TAG, "there are " + list.size() + " items in the list");
        return list;

    }

    /**
     * get all items from the TABLE_MAIN table
     * @return returns a list with all items, returns an empty list when no items are in the table.
     */
    public ArrayList<Item> getAllMainListItems() {

        Cursor cursor = db.query(TABLE_MAIN, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        ArrayList<Item> list = new ArrayList<Item>();

        try {

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    Item item = getItemById(cursor.getInt(MAIN_COLUMN_ITEMID));
                    item.unit = cursor.getString(MAIN_COLUMN_AMOUNT).charAt(cursor.getString(MAIN_COLUMN_AMOUNT).length() - 1);
                    double newAmount = Double.parseDouble(cursor.getString(MAIN_COLUMN_AMOUNT).replaceAll("[a-z]*", ""));
                    item.price *= newAmount / item.amount;
                    item.amount = newAmount;
                    item.bought = cursor.getInt(MAIN_COLUMN_BOUGHT) == 1;
                    item.category = getCategoryById(cursor.getInt(MAIN_COLUMN_CATEGORY));
                    boolean add = true;
                    for (int i = 0; i < list.size(); i++) {
                        if (list.get(i).id == item.id) {
                            add = false;
                            list.get(i).amount += item.amount;
                            list.get(i).price += item.price;
                            break;
                        }
                    }
                    if (add) {
                        list.add(item);
                    }
                    Log.d(TAG, "ID: " + cursor.getInt(MAIN_COLUMN_ITEMID) + ", amount: " + item.amount);
                } while (cursor.moveToNext());
            }


            // sort list
            Collections.sort(list);

        } catch (Exception e) {
            Log.d(TAG, "getAllMainListItems : " + e.toString());
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        return list;

    }

    /**
     * get all recipes from the TABLE_RECIPE table
     * @return returns a list with all recipes, returns an empty list if no recipes are in the table
     */
    public ArrayList<Recipe> getAllRecipes() {

        Cursor cursor = db.query(TABLE_RECIPE, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        ArrayList<Recipe> list = new ArrayList<Recipe>();

        try {

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    Recipe rec = new Recipe(cursor.getString(RECIPE_COLUMN_NAME));
                    try {
                        rec.rating = Double.parseDouble(cursor.getString(RECIPE_COLUMN_RATING));
                    } catch (Exception e) {
                        Log.d(TAG, "error parsing the rating");
                    }
                    rec.lastBought = cursor.getLong(RECIPE_COLUMN_LASTBOUGHT);
                    Log.d(TAG, "lastBought: " + rec.lastBought);
                    rec.id = cursor.getInt(RECIPE_COLUMN_ID);
                    Log.d(TAG, "ingreds: " + rec.id);
                    String ingreds = cursor.getString(RECIPE_COLUMN_ITEMS);
                    Log.d(TAG, "ingreds: " + ingreds);
                    String amounts = cursor.getString(RECIPE_COLUMN_AMOUNT);
                    Log.d(TAG, "amounts: " + amounts);
                    Pattern p = Pattern.compile("\\d*\\.\\w*");
                    Matcher m = p.matcher(amounts);
                    Log.d(TAG, "amounts: " + amounts);

                    ArrayList<String> amountList = new ArrayList<String>();

                    while (m.find()) {
                        amountList.add(m.group());
                    }

                    p = Pattern.compile("\\d+");
                    m = p.matcher(ingreds);
                    int i = 0;
                    while (m.find()) {
                        Item item = getItemById(Integer.parseInt(m.group()));
                        double oldAmount = item.amount;

                        Pattern q = Pattern.compile("\\d*\\.\\d*");
                        Matcher n = q.matcher(amountList.get(i));

                        if (n.find()) {
                            Log.d(TAG, n.group());
                            item.amount = Double.parseDouble(n.group());
                        }
                        Log.d(TAG, "amount#" + i + ": " + amountList.get(i));
                        item.price *= item.amount / oldAmount;
                        item.unit = amountList.get(i).charAt(amountList.get(i).length() - 1);
                        item.category = getCategoryByName(rec.name);
                        i++;
                        rec.addItem(item);
                    }
                    Log.d(TAG, "(getAllRecipes())recipe: " + rec);
                    list.add(rec);
                } while (cursor.moveToNext());
            }

            // sort list
            Collections.sort(list);

        } catch (Exception e) {
            e.printStackTrace();
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        return list;

    }

    /**
     * Get an item by barcode
     * @param barcode the barcode of the item
     * @return returns an item if one was found, null otherwise
     */
    public Item getItemByBarcode(long barcode) {

        String query = "SELECT * FROM " + TABLE_ITEM + " WHERE " + KEY_BARCODE + " = \"" + barcode + "\"";
        Cursor cursor = db.rawQuery(query, null);
        unclosedCursors.add(cursor);
        if (cursor.moveToFirst()) {
            Item item = new Item(
                    cursor.getString(DBAdapter.ITEM_COLUMN_NAME),
                    Double.parseDouble(cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).replaceFirst("[a-z]", "")),
                    Double.parseDouble(cursor.getString(DBAdapter.ITEM_COLUMN_PRICE)),
                    cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).charAt(cursor.getString(DBAdapter.ITEM_COLUMN_AMOUNT).length() - 1),
                    Uri.parse(cursor.getString(DBAdapter.ITEM_COLUMN_PHOTO)),
                    cursor.getLong(DBAdapter.ITEM_COLUMN_LASTBOUGHT),
                    cursor.getInt(DBAdapter.ITEM_COLUMN_PERIOD));
            item.id = cursor.getInt(DBAdapter.ITEM_COLUMN_ID);
            item.barcode = cursor.getLong(DBAdapter.ITEM_COLUMN_BARCODE);
            item.bought = cursor.getInt(DBAdapter.ITEM_COLUMN_BOUGHT) == 1;
            item.category = getCategoryById(cursor.getInt(ITEM_COLUMN_CATEGORY));
            cursor.close();
            unclosedCursors.remove(cursor);
            return item;
        }

        cursor.close();
        unclosedCursors.remove(cursor);
        return null;

    }

    public int getCategoryIdByName(String name) {
        ArrayList<Category> cats = getCategories();
        for (Category cat : cats) {
            if (cat.name.equals(name)) {
                return cat.id;
            }
        }
        return -1;
    }

    /**
     * Add a category to the TABLE_Category
     * @param category the category to be added to the table
     * @return return the categoryid
     */
    public void addCategory(Category category) {
        if (category.id != Category.NEW_CATEGORY && category.id != Category.UNCATEGORIZED) {
            db.execSQL("INSERT INTO " + TABLE_CATEGORY + " (" + KEY_NAME + ", " + KEY_COLOR + ", " + KEY_RECIPE + ") VALUES (\"" + category.name + "\", \"" + category.color + "\", \"" + (category.isRecipe ? 1 : 0) + "\");");
        }
    }

    /**
     * Add an item to the TABLE_ITEM
     * @param item the item to be added to the table
     */
    public void addItem(Item item) {
        String image = "";
        if (item.image != null) {
            image = item.image.toString();
        }
        db.execSQL("INSERT INTO " + TABLE_ITEM + " (" + KEY_NAME + ", " + KEY_AMOUNT + ", " + KEY_BOUGHT + ", " + KEY_PRICE + ", " + KEY_PHOTO + ", " + KEY_LASTBOUGHT + ", " + KEY_PERIOD + ", " + KEY_BARCODE + ", " + KEY_CATEGORYID + ") VALUES (\"" + item.name + "\", \"" + item.amount + item.unit + "\", \"" + 0 + "\", \"" + item.price + "\", \"" + image + "\", \"" + item.lastbought + "\", \"" + item.avgperiod + "\", \"" + item.barcode + "\", \"" + item.category.id + "\");");
    }

    /**
     * Add an item to the TABLE_ITEM, but add the id of the objet instead of generating one
     * @param item the item to be added to the table
     */
    public void addItemWithID(Item item) {
        String image = "";
        if (item.image != null) {
            image = item.image.toString();
        }
        db.execSQL("INSERT INTO " + TABLE_ITEM + " (" + KEY_ID + ", " +KEY_NAME + ", " + KEY_AMOUNT + ", " + KEY_BOUGHT + ", " + KEY_PRICE + ", " + KEY_PHOTO + ", " + KEY_LASTBOUGHT + ", " + KEY_PERIOD + ", " + KEY_BARCODE + ", " + KEY_CATEGORYID + ", " + KEY_BOUGHT +") VALUES (\""+ item.id + "\", \"" + item.name + "\", \"" + item.amount + item.unit + "\", \"" + 0 + "\", \"" + item.price + "\", \"" + image + "\", \"" + item.lastbought + "\", \"" + item.avgperiod + "\", \"" + item.barcode + "\", \"" + item.category.id  + "\", \"" + item.boughtcount + "\");");
    }
    
    /**
     * Add a recipe to the TABLE_RECIPE
     * @param rec the recipe to be added to the table
     */
    public void addRecipe(Recipe rec) {
        String items = "";
        String amounts = "";
        for (int i = 0; i < rec.ingredients.size(); i++) {
            items += rec.ingredients.get(i).id + ", ";
            amounts += rec.ingredients.get(i).amount;
            amounts += rec.ingredients.get(i).unit + ", ";
        }
        db.execSQL("INSERT INTO " + TABLE_RECIPE + " (" + KEY_NAME + ", " + KEY_ITEMS + ", " + KEY_AMOUNT + ", " + KEY_LASTBOUGHT + ", " + KEY_RATING + ") VALUES (\"" + rec.name + "\", \"" + items + "\", \"" + amounts + "\", \"0\", \"" + rec.rating + "\");");
        addCategory(new Category(Category.ADD_CATEGORY, rec.name, Category.COLOR_RANDOM, true));
    }

    /**
     * Add item to the main list
     * @param item the item to be added to the list
     */
    public void addToMainList(Item item) {
        db.execSQL("INSERT INTO " + TABLE_MAIN + " (" + KEY_ITEMID + ", " + KEY_AMOUNT + ", " + KEY_BOUGHT + ", " + KEY_CATEGORYID + ") VALUES (\"" + item.id + "\", \"" + item.amount + "" + item.unit + "\", \"" + (item.bought ? "1" : "0") + "\", \"" + item.category.id + "\");");
    }

    /** 
     * remove item from the main list by id
     * @param item the item to be removed from the list
     */
    public void removeFromMainList(Item item) {
        removeFromMainList(item.id);
    }

    /** 
     * remove item from the main list by id
     * @param itemID the id of the item
     */
    public void removeCategory(Category category) {
        db.execSQL("DELETE FROM " + TABLE_CATEGORY + " WHERE " + KEY_ID + " = \"" + category.id + "\"");
    }

    /** 
     * remove item from the main list by id
     * @param itemID the id of the item
     */
    public void removeFromMainList(int itemID) {
        db.execSQL("DELETE FROM " + TABLE_MAIN + " WHERE " + KEY_ITEMID + " = \"" + itemID + "\"");
    }

    /**
     * remove an item from the history
     * @param item the item to be removed
     */
    public void removeFromHistory(Item item) {
        db.execSQL("DELETE FROM " + TABLE_HISTORY + " WHERE " + KEY_ID + " = \"" + item.historyid + "\"");
    }

    /**
     * remove all items from the main list
     */
    public void clearMainList() {
        db.delete(TABLE_MAIN, null, null);
    }

    /**
     * remove all items from the history
     */
    public void clearHistory() {
        db.delete(TABLE_HISTORY, null, null);
    }

    /**
     * remove all items from the item table
     */
    public void clearItemList() {
        db.delete(TABLE_ITEM, null, null);
    }

    /**
     * remove all recipes from the recipe table
     */
    public void clearRecipeList() {
        Log.d(TAG, TABLE_RECIPE + " cleared");
        db.delete(TABLE_RECIPE, null, null);
    }

    /**
     * Get the next id, that was not assignet to an item before
     * @return return the next free id
     */
    public int getNextFreeItemId() {
        Cursor cursor = db.query(TABLE_ITEM, null, null, null, null, null, null);
        unclosedCursors.add(cursor);
        int id = -1;
        try {

            // get list from db
            if (cursor.moveToFirst()) {
                do {
                    int newId = cursor.getInt(DBAdapter.ITEM_COLUMN_ID);
                    if (newId >= id) {
                        id = newId + 1;
                    }
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
        }
        cursor.close();
        unclosedCursors.remove(cursor);
        return id;
    }

    /**
     * updates the properties of a category in the TABLE_CATEGORY
     * @param item 
     */
    public void updateCategory(Category category) {
        db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + KEY_NAME + " = \"" + category.name + "\" WHERE " + KEY_ID + " = " + category.id);
        db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + KEY_COLOR + " = \"" + category.color + "\" WHERE " + KEY_ID + " = " + category.id);
        db.execSQL("UPDATE " + TABLE_CATEGORY + " SET " + KEY_RECIPE + " = \"" + (category.isRecipe ? 1 : 0) + "\" WHERE " + KEY_ID + " = " + category.id);
    }

    /**
     * updates the properties of an item in the TABLE_ITEM
     * @param item 
     */
    public void updateItem(Item item) {
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_NAME + " = \"" + item.name + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_AMOUNT + " = \"" + item.amount + item.unit + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_PRICE + " = \"" + item.price + "\" WHERE " + KEY_ID + " = " + item.id);
        if (item.image != null) {
            db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_PHOTO + " = \"" + item.image.toString() + "\" WHERE " + KEY_ID + " = " + item.id);
        }
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_BARCODE + " = \"" + item.barcode + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_BOUGHT + " = \"" + item.boughtcount + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_CATEGORYID + " = \"" + item.category.id + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_LASTBOUGHT + " = \"" + item.lastbought + "\" WHERE " + KEY_ID + " = " + item.id);
    }

    /**
     * updates the properties of a recipe in the TABLEÏ
     * @param recipe 
     */
    public void updateRecipe(Recipe recipe) {
        // update recipe-category for recipe
        Recipe oldRecipe = getRecipeById(recipe.id);
        Category category = getCategoryById(getCategoryIdByName(oldRecipe.name));
        category.name = recipe.name;
        updateCategory(category);

        // update recipe
        String items = "";
        String amounts = "";
        for (int i = 0; i < recipe.ingredients.size(); i++) {
            items += recipe.ingredients.get(i).id + ", ";
            amounts += recipe.ingredients.get(i).amount;
            amounts += recipe.ingredients.get(i).unit + ", ";
        }
        db.execSQL("UPDATE " + TABLE_RECIPE + " SET " + KEY_NAME + " = \"" + recipe.name + "\" WHERE " + KEY_ID + " = " + recipe.id);
        db.execSQL("UPDATE " + TABLE_RECIPE + " SET " + KEY_RATING + " = \"" + recipe.rating + "\" WHERE " + KEY_ID + " = " + recipe.id);
        db.execSQL("UPDATE " + TABLE_RECIPE + " SET " + KEY_ITEMS + " = \"" + items + "\" WHERE " + KEY_ID + " = " + recipe.id);
        db.execSQL("UPDATE " + TABLE_RECIPE + " SET " + KEY_AMOUNT + " = \"" + amounts + "\" WHERE " + KEY_ID + " = " + recipe.id);
        db.execSQL("UPDATE " + TABLE_RECIPE + " SET " + KEY_LASTBOUGHT + " = \"" + recipe.lastBought + "\" WHERE " + KEY_ID + " = " + recipe.id);
    }

    /**
     * remove recipe from the TABLE_RECIPE
     * @param id the recipe id
     */
    public void removeFromRecipeList(int id) {
        db.execSQL("DELETE FROM " + TABLE_RECIPE + " WHERE " + KEY_ID + " = \"" + id + "\"");
    }

    /**
     * remove recipe from the TABLE_RECIPE
     * @param recipe the recipe to be removed
     */
    public void removeFromRecipeList(Recipe recipe) {
        removeFromRecipeList(recipe.id);
    }

    /**
     * remove item from the TABLE_ITEM
     * @param id the id of the item to be removed
     */
    public void removeFromItemList(int id) {
        db.execSQL("DELETE FROM " + TABLE_ITEM + " WHERE " + KEY_ID + " = \"" + id + "\"");
    }

    /**
     * remove item from the TABLE_ITEM
     * @param id the item to be removed
     */
    public void removeFromItemList(Item item) {
        removeFromItemList(item.id);
    }

    /**
     * do things that need to be done when an item is bought.
     * this method is not a callback method.
     * @param item the item bought
     */
    public void onItemBought(Item item) {
        db.execSQL("UPDATE " + TABLE_MAIN + " SET " + KEY_BOUGHT + " = \"" + (item.bought ? 1 : 0) + "\" WHERE " + KEY_ITEMID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_LASTBOUGHT + " = \"" + item.lastbought + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_PERIOD + " = \"" + item.avgperiod + "\" WHERE " + KEY_ID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_ITEM + " SET " + KEY_BOUGHT + " = \"" + item.boughtcount + "\" WHERE " + KEY_ID + " = " + item.id);
    }

    /**
     * update the properties of an item in the main list
     * @param item the item to be updated
     */
    public void updateMainItem(Item item) {
        db.execSQL("UPDATE " + TABLE_MAIN + " SET " + KEY_AMOUNT + " = \"" + item.amount + "" + item.unit + "\" WHERE " + KEY_ITEMID + " = " + item.id);
        db.execSQL("UPDATE " + TABLE_MAIN + " SET " + KEY_CATEGORYID + " = \"" + item.category.id + "\" WHERE " + KEY_ITEMID + " = " + item.id);
    }
    
    public void restoreItemsTable(ArrayList<Item> items){
        clearItemList();
        for(Item item: items) addItemWithID(item);
    }
    
}
